﻿using OestsCommon.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Utils;

namespace OestsDataBase
{
    public class SubjectDAL
    {
        private static Subject ReadData(DataRow dr)
        {
            Subject subject = new Subject
            {
                Id = Int32.Parse(dr["id"].ToString()),
                //subject.MajorId = dr["majorId"].Equals(DBNull.Value) ? 0 : int.Parse(dr["majorId"].ToString());
                PostId = dr["postId"].Equals(DBNull.Value) ? 0 : int.Parse(dr["postId"].ToString()),
                ProjectId = dr["projectId"].Equals(DBNull.Value) ? 0 : int.Parse(dr["projectId"].ToString()),
                Title = dr["title"].ToString(),

                A = dr["a"].ToString(),
                B = dr["b"].ToString(),
                C = dr["c"].ToString(),
                D = dr["d"].ToString(),
                E = dr["e"].Equals(DBNull.Value) ? null : dr["e"].ToString(),

                Score = dr["defaultscore"].Equals(DBNull.Value) ? 0 : int.Parse(dr["defaultscore"].ToString()),
                Describle = dr["describle"].ToString(),
                Creator = dr["creator"].ToString(),

                UseCount = dr["useCount"].Equals(DBNull.Value) ? 0 : (int)dr["useCount"]
            };

            SubjectType type = (SubjectType)Enum.Parse(typeof(SubjectType), dr["subject_type"].ToString());
            subject.SubType = type;

            string answer = dr["answer"].ToString();
            subject.Answer = answer.ToCharList();
            return subject;
        }


        /// <summary>
        /// 返回 Identity
        /// </summary>
        /// <param name="subject"></param>
        /// <returns></returns>
        public static int AddSubject(Subject subject)
        {
            string strSql = "";
            string answer = string.Join(",", subject.Answer);
            int addprojectid = ProjectDAL.GetSubjectsIDByProjectID(subject.ProjectId);
            strSql = string.Format("insert into T_Subject (projectId, postId,subject_type,title, a, b, c,d,e,answer,defaultscore,describle,creator) " +
                                                              "values({0},  {1},  '{2}', '{3}','{4}','{5}','{6}','{7}','{8}','{9}',{10},'{11}','{12}');SELECT @@Identity",
             addprojectid, subject.PostId, subject.SubType.ToString(), subject.Title, subject.A, subject.B, subject.C, subject.D, subject.E, answer, subject.Score, subject.Describle, subject.Creator);

            object obj = SqlHelper.ExecuteScalar(strSql);
            return Int32.Parse(obj.ToString());
        }

        //public static List<Choice> GetData()
        //{
        //    List<Choice> list = new List<Choice>();
        //    string strSql = "select * from T_Subject_Choice ";
        //    DataTable dt = SqlHelper.ExecuteDataTable(strSql);
        //    if (dt == null) { return null; }
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        Choice choice = ReadData(dr);
        //        list.Add(choice);
        //    }
        //    return list;
        //}

        public static bool Delete(int choiceId)
        {
            string strSql = string.Format("delete from T_Subject where id = @id");
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", choiceId));
            if (n != 0)
            {
                return true;
            }
            return false;
        }

        public static bool Modify(Subject subject)
        {
            int addprojectid = ProjectDAL.GetSubjectsIDByProjectID(subject.ProjectId);
            string answer = string.Join(",", subject.Answer);
            string strSql = string.Format("update T_Subject set projectId={0},postId={1},title='{2}',a='{3}',b='{4}',c='{5}',d='{6}',e='{7}',answer='{8}' ,describle='{9}',creator='{10}' where id = @id",
           addprojectid, subject.PostId, subject.Title, subject.A, subject.B, subject.C, subject.D, subject.E, answer, subject.Describle, subject.Creator);

            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", subject.Id));
            if (n != 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 更新某个试题的使用次数 +1 返回最新的使用次数
        /// </summary>
        /// <param name="subId"></param>
        public static int UpdateUseCount(int subId)
        {
            //查询当前的次数
            string selSql = $"select useCount from T_Subject where id = {subId} ";
            object obj = SqlHelper.ExecuteScalar(selSql);
            int count = 0;
            if (!DBNull.Value.Equals(obj))//需要和DBNull判断
            {
                count = (int)obj;
            }
            string strSql = string.Format($"update T_Subject set useCount={count + 1} where id = {subId}");
            SqlHelper.ExecuteNonQuery(strSql);
            return count + 1;
        }

        public static Choice GetChoiceById(int id)
        {
            string strSql = "select * from T_Subject where id = @id ";
            DataTable dt = SqlHelper.ExecuteDataTable(strSql, new SqlParameter("@id", id));
            if (dt == null) { return null; }
            if (dt.Rows.Count == 0)
            {
                Exception e = new Exception(string.Format("在选择题库中找不到 id 为 “{0}” 的选择题", id.ToString()));
                throw e;
            }
            DataRow dr = dt.Rows[0];
            Choice choice = (Choice)ReadData(dr);
            return choice;
        }

        public static Choice GetOccChoiceById(int id)
        {
            string strSql = "select * from T_Subject#Occ where id = @id ";
            DataTable dt = SqlHelper.ExecuteDataTable(strSql, new SqlParameter("@id", id));
            if (dt == null) { return null; }
            if (dt.Rows.Count == 0)
            {
                Exception e = new Exception(string.Format("在选择题库中找不到 id 为 “{0}” 的选择题", id.ToString()));
                throw e;
            }
            DataRow dr = dt.Rows[0];
            var sub =ReadData(dr) ;
            Choice choice = new Choice()
            {
                Id = sub.Id,
                SubType = sub.SubType,
                Title = sub.Title,
                PostId = sub.PostId,
                ProjectId = sub.ProjectId,
                Answer = sub.Answer,
                A = sub.A,
                B = sub.B,
                C = sub.C,
                D = sub.D,
                E = sub.E
            };
            return choice;
        }


        public static List<Choice> GetOccChoices()
        {
            List<Choice> choices = new List<Choice>();

            string strSql = string.Format("select * from T_Subject#Occ");

            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt != null)
            {

                foreach (DataRow dr in dt.Rows)
                {
                    Subject sub = ReadData(dr);

                    Choice choice = new Choice()
                    {
                        Id = sub.Id,
                        SubType = sub.SubType,
                        Title = sub.Title,
                        PostId = sub.PostId,
                        ProjectId = sub.ProjectId,
                        Answer = sub.Answer,
                        A = sub.A,
                        B = sub.B,
                        C = sub.C,
                        D = sub.D,
                        E = sub.E
                    };

                    choices.Add(choice);
                }
            }
            return choices;


        }

        public static List<Choice> GetOccChoicesByTaskId(int taskid)
        {
            string strSql = string.Format("select subjectids from T_Task_15#Cognitive where id ={0} ", taskid);

            var data = SqlHelper.ExecuteScalar(strSql).ToString();

            var subIds = LitJson.JsonMapper.ToObject<List<int>>(data);

            if (subIds == null) return null;

            List<Choice> choices = new List<Choice>();
            foreach (var id in subIds)
            {
                var ch = GetOccChoiceById(id);
                if (ch != null)
                    choices.Add(ch);
            }

            return choices;
        }


        public static bool CheckExist(int id)
        {
            string strSql = "select Count(*) from T_Subject where id = @id";
            object obj = SqlHelper.ExecuteScalar(strSql);
            return ((int)obj > 0); ;
        }

        public static List<Subject> GetSubjectData()
        {
            List<Subject> list = new List<Subject>();
            string strSqlsubject = "select * from T_Subject ";

            DataTable dt = SqlHelper.ExecuteDataTable(strSqlsubject);
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    Subject subject = ReadData(dr);
                    list.Add(subject);
                }
            }
            return list;
        }



        public static Subject GetSubjectById(int id)
        {
            string strSql = "select * from T_Subject where id = @id";
            DataTable dt = SqlHelper.ExecuteDataTable(strSql, new SqlParameter("@id", id));
            if (dt == null) { return null; }
            if (dt.Rows.Count == 0)
            {
                Exception e = new Exception(string.Format("在选择题库中找不到 id 为 “{0}” 的题目", id.ToString()));
                throw e;
            }
            DataRow dr = dt.Rows[0];
            Subject subject = ReadData(dr);
            return subject;
        }
    }
}
